package com.bifrost.service.impl;

import com.bifrost.constant.OrderStatus;
import com.bifrost.constant.ShippingType;
import com.bifrost.entity.*;
import com.bifrost.exception.CancelOrderException;
import com.bifrost.jpa.DynamicQuery;
import com.bifrost.model.bo.OrderReturnBO;
import com.bifrost.model.order.OrderDetailRO;
import com.bifrost.model.ro.StatementRO;
import com.bifrost.model.vo.OrderItemVO;
import com.bifrost.model.vo.OrderVO;
import com.bifrost.model.vo.PageVO;
import com.bifrost.model.vo.report.OrderReportVO;
import com.bifrost.repository.CategoryRepository;
import com.bifrost.repository.MerchantStoreRepository;
import com.bifrost.repository.OrderItemRepository;
import com.bifrost.repository.OrderRepository;
import com.bifrost.repository.OrderStatusHistoryRepository;
import com.bifrost.repository.ProductBrandRepository;
import com.bifrost.repository.ProductSkuRepository;
import com.bifrost.service.AdminOrderService;
import com.bifrost.service.OrderService;
import com.bifrost.service.ProductSkuService;
import com.bifrost.util.DateUtils;
import com.bifrost.util.LocalDateUtils;
import com.bifrost.util.QueryUtils;
import io.jsonwebtoken.lang.Assert;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service("adminOrderService")
@Slf4j
public class AdminOrderServiceImpl implements AdminOrderService {
    private static final Logger LOGGER = LoggerFactory.getLogger(AdminOrderServiceImpl.class);

    @Autowired
    ProductSkuRepository productSkuRepository;
    @Autowired
    OrderItemRepository orderItemRepository;
    @Autowired
    OrderRepository orderRepository;
    @Autowired
    ProductBrandRepository productBrandRepository;
    @Autowired
    OrderService orderService;
    @Autowired
    CategoryRepository categoryRepository;

    @Autowired
    MerchantStoreRepository merchantStoreRepository;
    @Autowired
    DynamicQuery dynamicQuery;
    @Autowired
    OrderStatusHistoryRepository orderStatusHistoryRepository;
    @Autowired
    ProductSkuService productSkuService;
    /**
     * 分页查询所有订单信息
     *
     * @param orderNumber
     * @param orderStatus
     * @param merchantStoreIds
     * @param productCode
     * @param productName
     * @param startTime
     * @param endTime
     * @param pageCode         :当前页
     * @param pageSize         :单页显示条数   @return
     */
    @Override
    public PageVO findByPage(Long categoryId, String shippingType, String orderNumber, String orderStatus, List<Long> merchantStoreIds, String productCode, String productName, String startTime, String endTime, Integer pageCode, Integer pageSize) {
        Pageable pageable = new PageRequest(pageCode - 1, pageSize);
        Specification<OrderItem> specification = new Specification<OrderItem>() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();
                if (categoryId != null) {
                    //增加查询条件：类目Id
                    Category category = categoryRepository.findOne(categoryId);
                    List<Category> categoryList = categoryRepository.findAllByExternalIdLike(category.getExternalId().concat("%"));
                    CriteriaBuilder.In<Long> in = cb.in(root.get("categoryId"));
                    categoryList.forEach(category1 -> {
                        in.value(category1.getCategoryId());
                    });
                    predicates.add(in);
                }
                if (!StringUtils.isBlank(shippingType)) {
                    //增加查询条件：配送方式
                    Join<OrderItem, Order> join = root.join("order", JoinType.INNER);
                    Path<String> shippingTypeJoin = join.get("shippingType");
                    predicates.add(cb.equal(shippingTypeJoin, shippingType));
                }
                if (!StringUtils.isBlank(orderNumber)) {
                    //增加查询条件：订单号
                    Join<OrderItem, Order> join = root.join("order", JoinType.INNER);
                    Path<String> orderQueryNum = join.get("orderNumber");
                    predicates.add(cb.like(orderQueryNum, "%" + orderNumber.trim() + "%"));
                }
                if (!StringUtils.isBlank(orderStatus)) {
                    //增加查询条件：订单状态
                    Join<OrderItem, Order> join = root.join("order", JoinType.INNER);
                    Path<String> orderQueryStatus = join.get("orderStatus");
                    predicates.add(cb.equal(orderQueryStatus, orderStatus.trim()));
                }
                if (!StringUtils.isBlank(startTime)) {
                    //增加查询条件：开始时间
                    Join<OrderItem, Order> join = root.join("order", JoinType.INNER);
                    Path<Date> orderQueryStartCreateTime = join.get("createdTimestamp");
                    Date startDate = DateUtils.parse(startTime, DateUtils.DEFAULT_DATE_FORMAT);
                    predicates.add(cb.greaterThanOrEqualTo(orderQueryStartCreateTime, startDate));
                }
                if (!StringUtils.isBlank(endTime)) {
                    //增加查询条件：结束时间
                    Join<OrderItem, Order> join = root.join("order", JoinType.INNER);
                    Path<Date> orderQueryEndCreateTime = join.get("createdTimestamp");
                    Date endData = DateUtils.parse(endTime, DateUtils.DEFAULT_DATE_FORMAT);
                    predicates.add(cb.lessThanOrEqualTo(orderQueryEndCreateTime, endData));
                }
                if (CollectionUtils.isNotEmpty(merchantStoreIds)) {
                    Join<OrderItem, Order> join = root.join("order", JoinType.INNER);
                    Path<Long> merchantStoreId = join.get("merchantStore").get("merchantId");
                    CriteriaBuilder.In<Long> in = cb.in(merchantStoreId);
                    for (Long id : merchantStoreIds) {
                        in.value(id);
                    }
                    predicates.add(in);
                }
                if (!StringUtils.isBlank(productCode)) {
                    //增加查询条件：商品sap编码
                    predicates.add(cb.like(root.get("externalId"), "%" + productCode.trim() + "%"));
                }
                if (!StringUtils.isBlank(productName)) {
                    //增加查询条件，商品名
                    predicates.add(cb.like(root.get("skuName"), "%" + productName.trim() + "%"));
                }
                Predicate[] p = new Predicate[predicates.size()];
                return cb.and(predicates.toArray(p));
            }
        };
        Page<OrderItem> orderItemPage = orderItemRepository.findAll(specification, pageable);
        Long totalElement = orderItemPage.getTotalElements();
        List<OrderItemVO> orderItemVOList = new ArrayList<>();
        orderItemPage.forEach(orderItem -> {
            OrderVO orderVO = new OrderVO();
            //获取订单并给基本数据赋值
            Order order = orderItem.getOrder();
            orderVO.setOrderId(order.getOrderId());
            orderVO.setShippingType(order.getShippingType());
            order.setShippingModuleCode(order.getShippingModuleCode());
            orderVO.setOrderTotal(order.getOrderTotal());
            orderVO.setOutTradeNo(order.getOutTradeNo());
            orderVO.setOrderNumber(order.getOrderNumber());
            orderVO.setOrderStatus(order.getOrderStatus());
            orderVO.setPaymentModuleCode(order.getPaymentModuleCode());
            //超过7天未提货的自提订单，增加人工退款功能(取消订单功能)
            if (ShippingType.SELF_DELIVERY.equalsIgnoreCase(order.getShippingType()) && OrderStatus.CONFIRMED.equalsIgnoreCase(order.getOrderStatus())) {
                LocalDateTime limitTime = LocalDateUtils.dateTimeFromDate(order.getCreatedTimestamp()).plusDays(7);
                if (LocalDateUtils.dateTimeFromDate(new Date()).isAfter(limitTime)) {
                    orderVO.setCancancel(true);
                }
            }
            //给创建时间赋值
            orderVO.setCreatedTimestamp(DateUtils.formatDate(order.getCreatedTimestamp(), DateUtils.DEFAULT_DATE_FORMAT));
            //获取订单的门店信息
            MerchantStore merchantStore = order.getMerchantStore();
            if (merchantStore != null) {
                orderVO.setMerchantStoreMessage("[" + merchantStore.getExternalId() + "]" + merchantStore.getStoreName());
            }
            OrderItemVO orderItemVO = new OrderItemVO();
            BeanUtils.copyProperties(orderItem, orderItemVO);
            if (orderItem.getSkuId() != null) {
                ProductSku productSku = productSkuRepository.findOne(orderItem.getSkuId());
                if (productSku != null) {
                    //设置订单项skuCode
                    orderItemVO.setSkuCode(productSku.getSkuCode());
                    //设置订单项skuExternalId
                    orderItemVO.setSkuExternalId(productSku.getExternalId());
                    //设置订单项totalPrice
                    orderItemVO.setTotalPrice(orderItem.getSkuPrice().multiply(new BigDecimal(orderItem.getSkuQuantity())));
                    //设置订单项的分类名称
                    //获取品类名称,品牌名称
                    Category category = productSku.getCategory();
                    if (category != null) {
                        orderItemVO.setCustomerCategoryName(category.getCategoryName());
                    }
                    ProductBrand productBrand = productBrandRepository.findOne(productSku.getProductBrandId());
                    if (productBrand != null) {
                        orderItemVO.setCustomerBrandName(productBrand.getBrandName());
                    }
                }
            }
            orderItemVO.setOrderVO(orderVO);
            orderItemVOList.add(orderItemVO);
        });
        PageVO pageVO = new PageVO<>(pageCode, pageSize, totalElement.intValue(), orderItemVOList);
        return pageVO;

    }


    @Override
    public PageVO getOrders(String cStartTime, String cEndTime, Long categoryId, String shippingType, String orderNumber, String orderStatus, List<Long> merchantStoreIdList, String productCode, String productName, String startTime, String endTime, Integer pageCode, Integer pageSize) {
        Pageable pageable = new PageRequest(pageCode - 1, pageSize, new Sort(Sort.Direction.ASC, "orderNumber"));
        Specification<Order> specification = new Specification<Order>() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();

                if (!StringUtils.isBlank(cStartTime)) {
                    //增加查询条件：取消订单开始时间
                    Join<Order, OrderStatusHistory> join = root.join("orderStatusHistories", JoinType.INNER);
                    Path<Date> cancelDateJoin = join.get("dateAdded");
                    predicates.add(cb.greaterThanOrEqualTo(cancelDateJoin, DateUtils.parse(cStartTime, DateUtils.DEFAULT_DATE_FORMAT)));

                    Path<String> orderStatusJoin = join.get("status");
                    predicates.add(cb.equal(orderStatusJoin,"CANCELED"));
                }

                if (!StringUtils.isBlank(cEndTime)) {
                    //增加查询条件：取消订单结束时间
                    Join<Order, OrderStatusHistory> join = root.join("orderStatusHistories", JoinType.INNER);
                    Path<Date> cancelDateJoin = join.get("dateAdded");
                    predicates.add(cb.lessThanOrEqualTo(cancelDateJoin,DateUtils.parse(cEndTime, DateUtils.DEFAULT_DATE_FORMAT)));

                    Path<String> orderStatusJoin = join.get("status");
                    predicates.add(cb.equal(orderStatusJoin,"CANCELED"));
                }

                if (!StringUtils.isBlank(shippingType)) {
                    //增加查询条件：配送方式
                    predicates.add(cb.equal(root.get("shippingType"), shippingType));
                }
                if (!StringUtils.isBlank(orderNumber)) {
                    //增加查询条件：订单号
                    predicates.add(cb.like(root.get("orderNumber"), "%" + orderNumber.trim() + "%"));
                }
                if (!StringUtils.isBlank(orderStatus)) {
                    //增加查询条件：订单状态
                    predicates.add(cb.equal(root.get("orderStatus"), orderStatus.trim()));
                }
                if (!StringUtils.isBlank(startTime)) {
                    //增加查询条件：开始时间
                    Date sDate = DateUtils.parse(startTime, DateUtils.DEFAULT_DATE_FORMAT);
                    predicates.add(cb.greaterThanOrEqualTo(root.get("createdTimestamp"), sDate));
                }
                if (!StringUtils.isBlank(endTime)) {
                    //增加查询条件：结束时间
                    Date eData = DateUtils.parse(endTime, DateUtils.DEFAULT_DATE_FORMAT);
                    predicates.add(cb.lessThanOrEqualTo(root.get("createdTimestamp"), eData));
                }
                if (CollectionUtils.isNotEmpty(merchantStoreIdList)) {
                    //增加查询条件：门店
                    CriteriaBuilder.In<Long> in = cb.in(root.get("merchantStore").get("merchantId"));
                    for (Long id : merchantStoreIdList) {
                        in.value(id);
                    }
                    predicates.add(in);
                }
                if (categoryId != null) {
                    //增加查询条件：类目Id
                    Join<Order, OrderItem> join = root.join("orderItems", JoinType.INNER);
                    Category category = categoryRepository.findOne(categoryId);
                    List<Category> categoryList = categoryRepository.findAllByExternalIdLike(category.getExternalId().concat("%"));
                    CriteriaBuilder.In<Long> in = cb.in(join.get("categoryId"));
                    categoryList.forEach(category1 -> {
                        in.value(category1.getCategoryId());
                    });
                    predicates.add(in);
                }
                if (!StringUtils.isBlank(productCode)) {
                    //增加查询条件：商品sap编码
                    Join<Order, OrderItem> join = root.join("orderItems", JoinType.INNER);
                    Path<String> externalIdJoin = join.get("externalId");
                    predicates.add(cb.equal(externalIdJoin, productCode));
                }
                if (!StringUtils.isBlank(productName)) {
                    //增加查询条件，商品名
                    Join<Order, OrderItem> join = root.join("orderItems", JoinType.INNER);
                    Path<String> externalIdJoin = join.get("sku_name");
                    predicates.add(cb.like(externalIdJoin, "%" + productName.trim() + "%"));
                }
                Predicate[] p = new Predicate[predicates.size()];
                return cb.and(predicates.toArray(p));
            }
        };
        Page page = orderRepository.findAll(specification, pageable);
        if(page.hasContent()){
            List<Order> data = page.getContent();
            if (CollectionUtils.isNotEmpty(data)) {
                List<OrderVO> orderVOList = data.stream().map(o -> convertToOrderVO(o, false)).collect(Collectors.toList());
                if (CollectionUtils.isNotEmpty(orderVOList)) {
                    return new PageVO<OrderVO>(pageCode, pageSize, ((int) (page.getTotalElements())), orderVOList);
                }
            }
        }
        return null;
    }

    private OrderVO convertToOrderVO(Order o, boolean usedForDetail){
        OrderVO vo = new OrderVO();
        MerchantStore store = o.getMerchantStore();
        vo.setOrderId(o.getOrderId());
        vo.setOrderNumber(o.getOrderNumber());
        vo.setOrderStatus(o.getOrderStatus());
        vo.setStoreId(store.getMerchantId());
        vo.setStoreCode(store.getStoreCode());
        vo.setStoreName(store.getStoreName());
        vo.setTotleFee(o.getOrderTotal());
        vo.setOrderTotal(o.getSubTotal());
        vo.setShipTotal(o.getTotalShippingFee());
        vo.setOutTradeNo(o.getOutTradeNo());
        vo.setShippingType(o.getShippingType());
        vo.setCreatedTimestamp(DateUtils.format(o.getCreatedTimestamp(),DateUtils.DEFAULT_DATE_FORMAT));
        if(OrderStatus.CANCELED.equalsIgnoreCase(o.getOrderStatus())){
            Date cancelDate = orderStatusHistoryRepository.getTimeOfOrder(o.getOrderId(), OrderStatus.CANCELED);
            if(null != cancelDate)
                vo.setCancelDate(DateUtils.format(cancelDate, DateUtils.DEFAULT_DATE_FORMAT));
        }
        //超过7天未提货的自提订单，增加人工退款功能(取消订单功能)
        if (ShippingType.SELF_DELIVERY.equalsIgnoreCase(o.getShippingType()) && OrderStatus.CONFIRMED.equalsIgnoreCase(o.getOrderStatus())) {
            LocalDateTime limitTime = LocalDateUtils.dateTimeFromDate(o.getCreatedTimestamp()).plusDays(7);
            if (LocalDateUtils.dateTimeFromDate(new Date()).isAfter(limitTime)) {
                vo.setCancancel(true);
            }
        }
        //如果用做订单明细
        if(usedForDetail) {
            List<OrderItem> orderItemList = o.getOrderItems();
            List<OrderItemVO> orderItemVOList = new ArrayList<>();
            for (OrderItem oi : orderItemList) {
                OrderItemVO oiVO = new OrderItemVO();
                oiVO.setOrderItemId(oi.getOrderItemId());
                ProductSku sku = productSkuRepository.findOne(oi.getSkuId());
                oiVO.setSkuExternalId(oi.getExternalId());
                oiVO.setSkuName(sku.getSkuName());
                oiVO.setSkuId(sku.getSkuId());
                oiVO.setSkuQuantity(oi.getSkuQuantity());
                oiVO.setOption(productSkuService.getSkuOptions(oi.getSkuId()));
                oiVO.setUnit(sku.getUnit());
                oiVO.setSkuPrice(oi.getSkuPrice());
                oiVO.setTotalPrice(oi.getSkuPrice());
                Category category = categoryRepository.getOne(oi.getCategoryId());
                oiVO.setCustomerCategoryName(category.getCategoryName());
                ProductBrand brand = productBrandRepository.findOne(sku.getProductBrandId());
                oiVO.setCustomerBrandName(brand.getBrandName());
                orderItemVOList.add(oiVO);
            }
            vo.setOrderItemVOList(orderItemVOList);
            if (!OrderStatus.PURCHASED.equalsIgnoreCase(o.getOrderStatus())) {
                Date paidDate = orderStatusHistoryRepository.getTimeOfOrder(o.getOrderId(), OrderStatus.CONFIRMED);
                vo.setPaidDate(DateUtils.formatDate(paidDate, DateUtils.DEFAULT_DATE_FORMAT));
            }
            if(OrderStatus.CANCELED.equalsIgnoreCase(o.getOrderStatus())) {
                Date cancelDate = orderStatusHistoryRepository.getTimeOfOrder(o.getOrderId(), OrderStatus.CANCELED);
                vo.setCancelDate(DateUtils.formatDate(cancelDate, DateUtils.DEFAULT_DATE_FORMAT));
            }
            vo.setPayment(o.getPayment().getDescription());
            vo.setCustomer(o.getCustomer().getCustomerLastname());
            vo.setReceive(o.getDeliveryLastName());
            vo.setReceivePhone(o.getDeliveryPhoneSecondary());
            CountryArea state = o.getDeliveryState();
            CountryArea city = o.getDeliveryCity();
            CountryArea district = o.getDeliveryDistrict();
            if(null != state && null != city && null != district)
                vo.setReceiveAddr(state.getName() + city.getName() + district.getName() + o.getDeliveryStreet());
            else
                vo.setReceiveAddr(o.getDeliveryStreet());
            vo.setPrintCount(o.getPrintCount());
            if(Boolean.TRUE.equals(o.getNeedInvoice())){
                vo.setInvoiceTitle(o.getInvoiceTitle());
                vo.setTaxNumber(o.getTaxNumber());
            }
        }
        return vo;
    }

    /**
     * 返回订单明细
     *
     * @param orderId orderId
     * @return OrderVO
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public OrderVO getOrderVO(Long orderId) {
        Order order = orderRepository.findOne(orderId);
        return null != order ? convertToOrderVO(order, true) : null;
    }

    /**
     * 打印取货单
     *
     * @param orderId orderId
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updatePrintCount(Long orderId) {
        orderRepository.updatePrintCount(orderId);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public PageVO getSellDetailReport(User user, StatementRO statementRO, int page, int pageSize, boolean exportFlag) {
        long startTime = System.currentTimeMillis();    //获取开始时间
        List<Long> merchantIds = QueryUtils.getMerchantIds(user, statementRO.getMerchantStoreIds());
        if (CollectionUtils.isEmpty(merchantIds)) {
            merchantStoreRepository.findAll().stream().forEach(merchantStore -> {
                merchantIds.add(merchantStore.getMerchantId());
            });
        }

        Map<String, Object> orderMap = getOrderMainSellDetail(merchantIds, statementRO, page, pageSize, exportFlag);
        Map<String, OrderReturnBO> orderReturnMap = getOrderReturnSellDetail(merchantIds, statementRO);
        List<OrderReportVO> vos = new ArrayList<>(orderMap.size());

        int recordCount = (Integer) orderMap.get("count");
        orderMap.remove("count");

        if (MapUtils.isNotEmpty(orderMap)) {
            for (String key : orderMap.keySet()) {
                OrderReportVO vo = (OrderReportVO) orderMap.get(key);
                if (orderReturnMap.containsKey(key)) {
                    OrderReturnBO bo = orderReturnMap.get(key);
                    vo.setSalesAmount(vo.getSalesAmount() - bo.getSalesAmount());
                    vo.setSalesQuantity(vo.getSalesQuantity() - bo.getSalesQuantity());
                }
                vos.add(vo);
            }
        }
        PageVO pageVO = new PageVO(page, pageSize, recordCount, vos);
        long endTime = System.currentTimeMillis();    //获取结束时间
        log.info("销售明细报表获取运行时间：" + (endTime - startTime) + "ms");    //输出程序运行时间
        return pageVO;
    }


    public Map<String, Object> getOrderMainSellDetail(List<Long> merchantIds, StatementRO statementRO, int page, int pageSize, boolean exportFlag) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT SQL_CALC_FOUND_ROWS ms.STORE_CODE,ms.STORE_NAME,ps.EXTERNAL_ID,ps.SKU_NAME,p.EXTERNAL_CATEGORY,cat.CATEGORY_NAME,pb.PRODUCT_BRAND_ID,pb.BRAND_NAME,IFNULL(sum(oi.SKU_QUANTITY),0),IFNULL(sum(om.ORDER_TOTAL),0),ps.SKU_ID,om.MERCHANT_ID\n" +
                "FROM order_item oi\n" +
                "left join order_main om on om.ORDER_ID = oi.ORDER_ID and om.ORDER_STATUS not in ('PURCHASED','CANCELED') \n" +
                "left join product_sku ps on ps.SKU_ID = oi.SKU_ID\n" +
                "left join product p on ps.PRODUCT_ID = p.PRODUCT_ID\n" +
                "left join category cat on cat.CATEGORY_ID = ps.CATEGORY_ID\n" +
                "left join product_brand  pb on pb.PRODUCT_BRAND_ID = ps.PRODUCT_BRAND_ID \n" +
                "left join merchant_store ms on ms.MERCHANT_ID = om.MERCHANT_ID\n");
        sql.append("where om.MERCHANT_ID in (" + org.apache.commons.lang3.StringUtils.join(merchantIds, ",") + ")\n");
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getStartDate())) {
            sql.append("and om.CREATED_TIMESTAMP >= ? \n");
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getEndDate())) {
            sql.append("and om.CREATED_TIMESTAMP <= ? \n");
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getSkuName())) {
            sql.append("and ps.SKU_NAME like ? \n");
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getProductType())) {
            sql.append("and cat.CATEGORY_CODE like ? \n");
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getProductBrand())) {
            sql.append("and pb.BRAND_CODE like ? \n");
        }
        sql.append("GROUP BY om.MERCHANT_ID,ps.SKU_ID\n");
        if (!exportFlag) {
            sql.append("limit ?,?");
        }
        List<List<Object>> resultList = dynamicQuery.nativeQueryList(
                String.valueOf(sql),
                Boolean.valueOf(true),//true：为空不纳入查询条件
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getStartDate()) ? DateUtils.parse(statementRO.getStartDate(), DateUtils.DEFAULT_DATE_FORMAT) : null,
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getEndDate()) ? DateUtils.parse(statementRO.getEndDate(), DateUtils.DEFAULT_DATE_FORMAT) : null,//查询条件，销售时间段
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getSkuName()) ? statementRO.getSkuName() : null,
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getProductType()) ? statementRO.getProductType() + "%" : null,//品类
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getProductBrand()) ? statementRO.getProductBrand() + "%" : null,//品牌
                exportFlag ? null : (page - 1) * pageSize,
                exportFlag ? null : pageSize
        );
        StringBuilder builder = new StringBuilder();
        Map<String, Object> map = new HashMap<>();
        if (CollectionUtils.isNotEmpty(resultList)) {
            resultList.forEach(list -> {
                OrderReportVO reportVO = new OrderReportVO();
                int index = 0;
                reportVO.setStoreMsg("[" + String.valueOf(list.get(index++)) + "]" + String.valueOf(list.get(index++)));

                reportVO.setProductCode(String.valueOf(list.get(index++)));
                reportVO.setProductName(String.valueOf(list.get(index++)));
                reportVO.setCategoryName("[" + String.valueOf(list.get(index++)) + "]" + String.valueOf(list.get(index++)));
                reportVO.setBrandName("[" + String.valueOf(list.get(index++)) + "]" + String.valueOf(list.get(index++)));
                String salesQuantity = String.valueOf(list.get(index++));
                reportVO.setSalesQuantity(Integer.valueOf(salesQuantity));
                String salesAmount = String.valueOf(list.get(index++));
                reportVO.setSalesAmount(Double.valueOf(salesAmount));

                String productId = String.valueOf(list.get(index++));
                String merchantId = String.valueOf(list.get(index++));

                reportVO.setProductId(productId);

                map.put(builder.append(productId).append("+").append(merchantId).toString(), reportVO);
                builder.delete(0, builder.length());
            });
        }
        String newSql = "select FOUND_ROWS();";
        int count = 0;
        List<List<Object>> l = dynamicQuery.nativeQueryList(newSql, true);
        for (List list : l) {
            count = Integer.parseInt(String.valueOf(list.get(0)));
        }
        map.put("count", new Integer(count));
        return map;
    }


    public Map<String, OrderReturnBO> getOrderReturnSellDetail(List<Long> merchantIds, StatementRO statementRO) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ori.SKU_ID,or_.MERCHANT_STORE_ID,IFNULL(sum(ori.SKU_QUANTITY),0),IFNULL(sum(or_.REFUND_TOTAL),0) \n" +
                "FROM order_return_item ori \n" +
                "inner join order_return or_ on or_.ID = ori.ORDER_RETURN_ID \n");
        sql.append("where or_.MERCHANT_STORE_ID in (" + org.apache.commons.lang3.StringUtils.join(merchantIds, ",") + ")\n");
        sql.append("and or_.REFUND_STATUS = 'REFUNDED' \n");
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getStartDate())) {
            sql.append("and or_.CREATED_TIMESTAMP >= ? \n");
        }
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getEndDate())) {
            sql.append("and or_.CREATED_TIMESTAMP <= ? \n");
        }
        sql.append("GROUP BY or_.MERCHANT_STORE_ID,ori.SKU_ID \n");
        List<List<Object>> resultList = dynamicQuery.nativeQueryList(
                String.valueOf(sql),
                Boolean.valueOf(true),//true：为空不纳入查询条件
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getStartDate()) ? DateUtils.parse(statementRO.getStartDate(), DateUtils.DEFAULT_DATE_FORMAT) : null,
                org.apache.commons.lang3.StringUtils.isNotEmpty(statementRO.getEndDate()) ? DateUtils.parse(statementRO.getEndDate(), DateUtils.DEFAULT_DATE_FORMAT) : null//查询条件，销售时间段
        );
        StringBuilder builder = new StringBuilder();
        Map<String, OrderReturnBO> map = new HashMap<>();
        if (CollectionUtils.isNotEmpty(resultList)) {
            resultList.forEach(list -> {
                OrderReturnBO bo = new OrderReturnBO();
                int index = 0;
                String productId = String.valueOf(list.get(index++));
                String merchantId = String.valueOf(list.get(index++));

                String salesQuantity = String.valueOf(list.get(index++));
                bo.setSalesQuantity(Integer.valueOf(salesQuantity));
                String salesAmount = String.valueOf(list.get(index++));
                bo.setSalesAmount(Double.valueOf(salesAmount));

                map.put(builder.append(productId).append("+").append(merchantId).toString(), bo);
                builder.delete(0, builder.length());
            });
        }
        return map;
    }


    public void cancel(User user, Long orderId) throws CancelOrderException {
        Order order = orderService.findOrderById(orderId);
        Assert.notNull(order, "未找到订单相关信息");
        if (orderCanCancel(order)) {
            orderService.cancel(order.getCustomer(), orderId, String.format("自提订单超过7天未提货，管理员：%s 后台取消订单", "[" + user.getConfirmCode() + "]" + user.getAdminName()));
        }
    }

    private boolean orderCanCancel(Order order) throws CancelOrderException {
        //非自提订单
        if (!ShippingType.SELF_DELIVERY.equalsIgnoreCase(order.getShippingType())) {
            throw new CancelOrderException("订单非自提订单,不能取消该订单");
        }
        //时间未满7天
        if (LocalDate.now().minusDays(7).isBefore(LocalDateUtils.fromDate(order.getCreatedTimestamp()))) {
            throw new CancelOrderException("订单未超过7天,不能取消该订单");
        }
        return true;
    }

    /**
     * 订单明细报表
     * @param ro
     * @param merchantStoreIdList
     * @return
     */
    @Override
    public Page<OrderItem> listDetail(OrderDetailRO ro, List<Long> merchantStoreIdList) {
        Specification<OrderItem> specification = createOrderDetailSpecification(ro,merchantStoreIdList);
        Pageable pageable = new PageRequest(ro.getPageCode() - 1, ro.getPageSize(),
                new Sort(Sort.Direction.DESC, "orderItemId"));
        return orderItemRepository.findAll(specification,pageable);
    }

    @Override
    public List<OrderItem> exportDetail(OrderDetailRO ro, List<Long> merchantStoreIdList) {
        Specification<OrderItem> specification = createOrderDetailSpecification(ro,merchantStoreIdList);
        return orderItemRepository.findAll(specification);
    }

    private Specification<OrderItem> createOrderDetailSpecification(OrderDetailRO ro, List<Long> merchantStoreIdList) {
        return new Specification<OrderItem>() {
            @Override
            public Predicate toPredicate(Root<OrderItem> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();
                Join<OrderItem,Order> orderJoin = root.join("order", JoinType.INNER);
                if(CollectionUtils.isNotEmpty(merchantStoreIdList)){
                    Path<Long> merchantStoreId = orderJoin.get("merchantStore").get("merchantId");
                    CriteriaBuilder.In<Long> in = cb.in(merchantStoreId);
                    for(Long id : merchantStoreIdList){
                        in.value(id);
                    }
                    predicates.add(in);
                }
                if(!StringUtils.isBlank(ro.getOrderType())){
                    predicates.add(cb.equal(orderJoin.get("orderType"),ro.getOrderType()));
                }
                if(StringUtils.isNotBlank(ro.getPayment())){
                    predicates.add(cb.equal(orderJoin.get("payment").get("typeCode"),ro.getPayment()));
                }
                if(StringUtils.isNotEmpty(ro.getOrderStatus())){
                    predicates.add(cb.equal(orderJoin.get("orderStatus"),ro.getOrderStatus()));
                }
                if(ro.getStartDate() != null){
                    Date startDate = DateUtils.parse(ro.getStartDate(), DateUtils.DEFAULT_DATE_FORMAT);
                    predicates.add(cb.greaterThanOrEqualTo(orderJoin.get("datePurchased"),startDate));
                }
                if(ro.getEndDate() != null){
                    Date endData = DateUtils.parse(ro.getEndDate(), DateUtils.DEFAULT_DATE_FORMAT);
                    predicates.add(cb.lessThanOrEqualTo(orderJoin.get("datePurchased"),endData));
                }
                if(StringUtils.isNotEmpty(ro.getOrderNumber())){
                    predicates.add(cb.equal(orderJoin.get("orderNumber"),ro.getOrderNumber()));
                }
                if(StringUtils.isNotEmpty(ro.getSkuSAPCode())){
                    predicates.add(cb.equal(root.get("externalId"),ro.getSkuSAPCode()));
                }
                Predicate[] p = new Predicate[predicates.size()];
                return cb.and(predicates.toArray(p));
            }
        };
    }
}
